MySQL 常见的数据类型
整型
常用的整数数据类型有 tinyint ,smallint ,mediumint , int ,bigint 共计5种,在声明列时,后面也可以跟上 n ,例如 int(n) 。实际上这里的 n 非常鸡肋,几乎没有任何使用场景。它的含义是“显示位宽”,这个 n 无论填任何数,不影响存储环节,仅影响在检索时的输出格式,而且在非常严格的情况下才成立。
我们描述一种应用场景:我们声明某列(列名取int_5)为 int(5) ,在声明列的时候,要使用到该特性,必须加上 zerofill (填充0)属性,即语句为
`int_5` int(5) unsigned zerofill DEFAULT NULL
-- 备注:加zerofill必须同时加unsigned
当插入的数字小于5位时,在特定客户端检索输出时,会在数字前“补0”,凑足5位数字。(大于5位则原数字原样显示)例如存储的数字是123,那么输出 00123 。
说它鸡肋,主要有以下几个原因:
(1)对存储环节没有任何帮助,仅改变输出显示环节。而“格式化显示”一般在前端或者后端的应用层操作就可以了,无需在数据库中输出时操作。
(2)格式化方式仅仅只有“补0”一种方式。
(3)仅针对特定客户端输出时才有显示效果,目前仅发现使用 MySQL Shell 才有显示效果,其他客户端连接时均无。
由于以上原因,所以几乎没有开发者会使用这个特性。
类型 | 字节 | 范围 |
---|---|---|
TinyInt | 1 | -128~127 无符型:0~255 |
smallInt | 2 | -32768~32767 无符型:0~65535 |
mediumInt | 3 | |
Int | 4 | |
bigInt | 8 |
使用 unsigned
来设置无符号
create table 表名(
t1 int,
t2 int unsigned
)
参考阿里的 Java 开发手册 一般使用这些整型类型的场景
对象 | 年龄区间 | 类型 | 字节 | 表示范围 |
---|---|---|---|---|
人 | 150 岁之内 | tinyint unsigned | 1 | 无符号值:0 到 255 |
龟 | 数百岁 | smallint unsigned | 2 | 无符号值:0 到 65535 |
恐龙化石 | 数千万年 | int unsigned | 4 | 无符号值:0 到约 43 亿 |
太阳 | 约 50 亿年 | bigint unsigned | 8 | 无符号值:0 到约 10 的 19 次方 |
注意:参见其中一条:【强制】表必备三字段:id, create_time, update_time。
说明:其中 id 必为主键,类型为 bigint unsigned
、单表时自增、步长为 1
而 create_time
、update_time
的类型均为 datetime
类型,前者现在时表示主动式创建,后者过去分词表示被动式更新。*
一般像一些标识字段(例如 isDeleted
)使用的是 tinyint unsigned
浮点型
浮点型是有精度的,所以只会无限接近那个值
注意:【强制】小数类型为 decimal,禁止使用 float 和 double。
说明:在存储的时候,float 和 double 都存在精度损失的问题,很可能在比较值的时候,得到不正确的结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数并分开存储。
类型名称 | 说明 | 存储需求 |
---|---|---|
FLOAT | 单精度浮点数 | 4 个字节 |
DOUBLE | 双精度浮点数 | 8 个字节 |
DECIMAL (M, D),DEC | 压缩的“严格”定点数 | M+2 个字节 |
DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的,DECIMAL 可能的最大取值范围与 DOUBLE 相同,但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D,则取值范围将随 M 的变大而变大。
D:代表小数点后的位数 M:表示小数点前后的总位数
注意:float
和 double
都不需要定义 M 和 D,因为它们是自动根据插入数值的精度来做决定的,只有 decimal
才需要设置
字符型
常用的字符串类型的数据类型有 CHAR 和 VARCHAR 两种,两者后面都需要跟上一个数字表示长度,例如
CHAR(10)
VARCHAR(10)
CHAR(n) 和 VARCHAR(n) 两者中的 n 含义均为该字段最大可容纳的字符数。(注意早期的版本中,n指的是字节数,你也不需要关注是哪些版本,因为是十多年前的版本了,估计一般人也用不到)。
一般字符型的数据分两种:长文本、短文本
类型名称 | 说明 | 存储需求 |
---|---|---|
CHAR(M) | 固定长度非二进制字符串 | M 字节,1<=M<=255 |
VARCHAR(M) | 变长非二进制字符串 | L+1字节,在此,L< = M和 1<=M<=255 |
TINYTEXT | 非常小的非二进制字符串 | L+1字节,在此,L<2^8 |
TEXT | 小的非二进制字符串 | L+2字节,在此,L<2^16 |
MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节,在此,L<2^24 |
LONGTEXT | 大的非二进制字符串 | L+4字节,在此,L<2^32 |
ENUM | 枚举类型,只能有一个枚举字符串值 | 1或2个字节,取决于枚举值的数目 (最大值为65535) |
SET | 一个设置,字符串对象可以有零个或 多个SET成员 | 1、2、3、4或8个字节,取决于集合 成员的数量(最多64个成员) |
这个 (M)
指最大大小(Max)
VARCHAR 和 TEXT 类型是变长类型,其存储需求取决于列值的实际长度(在前面的表格中用 L 表示),而不是取决于类型的最大可能尺寸。
注意:char 是固定字符 varchar 是可变的(例如设定10个字符,但是只存两个字符,则自动只分配两个字符大小)。效率上 char 比 varchar 高,但是空间利用率上 varchar 更胜一筹,所以例如性别这种定长就可以用 char
TEXT 列保存非二进制字符串,如文章内容、评论等。当保存或查询 TEXT 列的值时,不删除尾部空格。
TEXT 类型分为 4 种:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度也不同。(具体看上面的表)
二进制
括号中的 M 表示可以为其指定长度
类型名称 | 说明 | 存储需求 |
---|---|---|
BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
BINARY(M) | 固定长度二进制字符串 | M 字节 |
VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
TINYBLOB (M) | 非常小的BLOB | L+1 字节,在此,L<2^8 |
BLOB (M) | 小 BLOB | L+2 字节,在此,L<2^16 |
MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节,在此,L<2^24 |
LONGBLOB (M) | 非常大的BLOB | L+4 字节,在此,L<2^32 |
日期型
类型 | 字节 | 最小值 | 最大值 | 是否受时区影响 |
---|---|---|---|---|
date | 4 | 1000-01-01 | 9999-12-31 | 受 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 不受 |
timestamp(时间戳) | 4 | 1970-01-01 08:00:01 | 2038某个时刻 | 受 |
time | 3 | -838:59:59 | 838:59:59 | 不受 |
year | 1 | 1901 | 2155 | 不受 |
注:timestamp
会和实际时区挂钩(自动根据不同地区的时区而改变数据),所以这个更能反应实际的日期
date 和 datetime 区别
- date 保存精度到天,格式为:
YYYY-MM-DD
如2016-11-07
- datetime 和 timestamp 精度保存到秒,格式为:
YYYY-MM-DD HH:MM:SS
如:2016-11-07 10:58:27